const db = require('../db/connMysql');

const mysql_tool = require('../util/mysql_tool');

const userDao = {
    /*
     * 用户使用账号密码登录的方法
     */
    // async loginByAccountName(accountName, password) {
    //     let result = await new Promise((resolve, reject) => {
    //         db.getConnection((err, connection) => {
    //             if (err) {
    //                 // 连不上数据库
    //                 reject(err);
    //                 return ;
    //             }
    //             let sql = 'select * from Users where accountName = ? and password = ? Limit 1';
    //             connection.query(sql, [accountName, password], function (err, result) {
    //                 if (err) {
    //                     // 连上数据库之后，执行sql时发生错误
    //                     reject(err);
    //                     return ;
    //                 }
    //                 connection.release();
    //                 resolve(result);
    //             });
    //         });
    //     });
    //     return result;
    // },

    /*
     * 用户使用账号密码登录的方法
     */
    // async loginByPhone(phone, password) {
    //     let result = await new Promise((resolve, reject) => {
    //         db.getConnection((err, connection) => {
    //             if (err) {
    //                 // 连不上数据库
    //                 reject(err);
    //                 return ;
    //             }
    //             let sql = `select
    //                            uid, username, UserType.name as userType, allowLogin
    //                        from
    //                            Users
    //                        left join
    //                            UserType
    //                        on UserType.userTypeId = Users.userTypeId
    //                        where phone = ? and password = ? Limit 1`;
    //             connection.query(sql, [phone, password], function (err, result) {
    //                 if (err) {
    //                     // 连上数据库之后，执行sql时发生错误
    //                     reject(err);
    //                     return ;
    //                 }
    //                 connection.release();
    //                 resolve(result);
    //             });
    //         });
    //     });
    //     return result;
    // },

    /*
     * 用户使用工号密码登录的方法
     */
    async loginByWorkId(workId, password) {
        console.log('【开始连接数据库的时间】', Date());
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                console.log('【数据库连接成功的时间】', Date());
                let sql = `select
                               uid, username, UserType.name as userType, allowLogin
                           from
                               Users
                           left join
                               UserType
                           on UserType.userTypeId = Users.userTypeId
                           where workId = ? and password = ? Limit 1`;
                connection.query(sql, [workId, password], function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });
        return result;
    },

    /*
     * 添加用户的方法
     */
    async addUser(userInfo) {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                let sql = `insert into Users (
                               accountName, username, workId, facultyId, password, phone, registerTime, userTypeId, email, allowLogin
                           ) values (
                               ?, ?, ?, ?, ?, ?, ?, (
                                   select userTypeId from UserType where name = ? LIMIT 1
                               ), ?, ?
                           )`;
                let data = [userInfo.accountName, userInfo.username, userInfo.workId, userInfo.facultyId, userInfo.password, userInfo.phone, userInfo.createTime, userInfo.userType, userInfo.email, userInfo.allowLogin];
                connection.query(sql, data, function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        console.log(err);
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });

        return result;
    },
    /*
     * 获取所有用户的方法
     */
    async getAllUserList() {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                let sql = `select
                               u.uid as uid,
                               u.accountName as accountName,
                               u.username as username,
                               u.workId as workId,
                               u.phone as phone,
                               ut.name as userType,
                               u.userTypeId as userTypeId,
                               f.name as faculty,
                               u.facultyId as facultyId,
                               DATE_FORMAT(u.registerTime, '%Y-%m-%d') as registerTime,
                               u.email as email,
                               u.allowLogin as allowLogin
                           from Users as u
                           left join UserType as ut on ut.userTypeId = u.userTypeId
                           left join Facultys as f on f.facultyId = u.facultyId`;
                connection.query(sql, [], function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });

        return result;
    },
    /*
     * 新增用户类型的方法
     */
    async addUserType(addUserTypeInfo) {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                let sql = `insert into UserType (name, isDefaultType, description) values (?, 'false', ?)`;
                connection.query(sql, [addUserTypeInfo.userTypeName, addUserTypeInfo.userTypeDesc], function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });
        let insertId = result.insertId;
        let return_obj = {success: true, msg: "添加用户类型成功", userTypeId: insertId};
        if (!addUserTypeInfo.userTypeBindingPower) {
            return_obj.msg += ", 但未绑定任何权限";
            return return_obj;
        }
        // 添加新用户类型对应的权限
        result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                let insert_values = '';
                for (let i = 0; i < addUserTypeInfo.userTypeBindingPower.length; i++) {
                    insert_values += ('(\'' + addUserTypeInfo.userTypeBindingPower[i] + '\', \'' + insertId + '\'), ');
                }
                if (insert_values.length > 0) {
                    insert_values = insert_values.substring(0, insert_values.length - 2);
                }
                let sql = `insert into UserTypePowerBinding (powerId, userTypeId) values ` + insert_values;
                connection.query(sql, [], function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });

        if (result.errno) {
            return_obj.msg += ', 但绑定权限时错误';
        } else {
            return_obj.msg += ', 绑定类型成功';
        }

        return return_obj;
    },
    /*
     * 获取所有用户类型的方法
     */
    async getAllUserTypeList() {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                let sql = `select userTypeId, name, description from UserType`;
                connection.query(sql, [], function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });

        return result;
    },
    /*
     * 获取所有辅导员列表
     */
    async getAllTeacherList() {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                let sql = `select u.uid, u.workId, u.username, t.teacherId as counselorId
                           from Users as u
                           left join Teachers as t on u.uid = t.uid
                           where u.userTypeId = (
                               select userTypeId from UserType where name = '辅导员' limit 1
                           )`;
                connection.query(sql, [], function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });

        return result;
    },
    /*
     * 修改用户信息，不包含密码
     */
    async changeUserInfo(uid, username, accountName, phone, email, facultyId) {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                let sql = `update Users
                           set username=?, accountName=?, phone=?, email=?, facultyId=?
                           where uid=?`;
                connection.query(sql, [username, accountName, phone, email, facultyId, uid], function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });

        return result;
    },
    /*
     * 修改用户信息和密码
     */
    async changeUserInfoAndPassword(uid, username, accountName, phone, email, facultyId, password) {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                let sql = `update Users
                           set username=?, accountName=?, phone=?, email=?, facultyId=?, password=?
                           where uid=?`;
                connection.query(sql, [username, accountName, phone, email, facultyId, password, uid], function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });

        return result;
    },
    async changeUserInfoAndUserType(uid, username, accountName, phone, email, facultyId, userTypeId) {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                // 开始事务
                connection.beginTransaction(async (err) => {
                    if (err) {
                        connection.release();
                        reject(err);
                        return;
                    }

                    // 修改用户信息
                    let sql1 = `update Users
                           set username=?, accountName=?, phone=?, email=?, facultyId=?, userTypeId=?
                           where uid=?`;
                    let data = [username, accountName, phone, email, facultyId, userTypeId, uid];
                    connection.query(sql1, data, (queryErr, result) => {
                        if (queryErr) {
                            errorOccurred = true;
                            console.error('修改用户信息错误:', queryErr);
                            // 如果有错误发生，则立即回滚事务
                            connection.rollback(() => {
                                connection.release();
                                reject(queryErr);
                            });
                            return;
                        }
                        
                        // 删除用户权限表里关于自己有关的所有权限
                        let sql2 = `delete from UserPower where uid=?`;
                        let data2 = [uid];
                        connection.query(sql2, data2, (queryErr, result) => {
                            if (queryErr) {
                                errorOccurred = true;
                                console.error('修改用户类型错误:', queryErr);
                                // 如果有错误发生，则立即回滚事务
                                connection.rollback(() => {
                                    connection.release();
                                    reject(queryErr);
                                });
                                return;
                            }

                            // 修改用户对应的权限
                            let sql3 = `insert into UserPower(uid, powerId) (
                                            select ? as uid, utpb.powerId as powerId from UserTypePowerBinding as utpb where utpb.userTypeId = ?
                                        )`;
                            let data3 = [uid, userTypeId];
                            connection.query(sql3, data3, (queryErr, result) => {
                                if (queryErr) {
                                    errorOccurred = true;
                                    console.error('修改用户类型错误:', queryErr);
                                    // 如果有错误发生，则立即回滚事务
                                    connection.rollback(() => {
                                        connection.release();
                                        reject(queryErr);
                                    });
                                    return;
                                }

                                // 如果所有插入操作都成功，则提交事务
                                connection.commit(err => {
                                    if (err) {
                                        // 如果提交出错，则回滚事务
                                        connection.rollback(() => {
                                            connection.release();
                                            reject(err);
                                        });
                                        return;
                                    }

                                    // 释放连接
                                    connection.release();
                                    resolve(true);
                                });
                            });
                        });
                    });
                });
            });
        });

        return result;
    },
    async changeUserInfoAndUserTypeAndPassword(uid, username, accountName, phone, email, facultyId, userTypeId, password) {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                // 开始事务
                connection.beginTransaction(async (err) => {
                    if (err) {
                        connection.release();
                        reject(err);
                        return;
                    }

                    // 修改用户信息
                    let sql1 = `update Users
                           set username=?, accountName=?, phone=?, email=?, facultyId=?, userTypeId=?, password=?
                           where uid=?`;
                    let data = [username, accountName, phone, email, facultyId, userTypeId, password, uid];
                    connection.query(sql1, data, (queryErr, result) => {
                        if (queryErr) {
                            errorOccurred = true;
                            console.error('修改用户信息错误:', queryErr);
                            // 如果有错误发生，则立即回滚事务
                            connection.rollback(() => {
                                connection.release();
                                reject(queryErr);
                            });
                            return;
                        }
                        
                        // 删除用户权限表里关于自己有关的所有权限
                        let sql2 = `delete from UserPower where uid=?`;
                        let data2 = [uid];
                        connection.query(sql2, data2, (queryErr, result) => {
                            if (queryErr) {
                                errorOccurred = true;
                                console.error('修改用户类型错误:', queryErr);
                                // 如果有错误发生，则立即回滚事务
                                connection.rollback(() => {
                                    connection.release();
                                    reject(queryErr);
                                });
                                return;
                            }

                            // 修改用户对应的权限
                            let sql3 = `insert into UserPower(uid, powerId) (
                                            select ? as uid, utpb.powerId as powerId from UserTypePowerBinding as utpb where utpb.userTypeId = ?
                                        )`;
                            let data3 = [uid, userTypeId];
                            connection.query(sql3, data3, (queryErr, result) => {
                                if (queryErr) {
                                    errorOccurred = true;
                                    console.error('修改用户类型错误:', queryErr);
                                    // 如果有错误发生，则立即回滚事务
                                    connection.rollback(() => {
                                        connection.release();
                                        reject(queryErr);
                                    });
                                    return;
                                }

                                // 如果所有插入操作都成功，则提交事务
                                connection.commit(err => {
                                    if (err) {
                                        // 如果提交出错，则回滚事务
                                        connection.rollback(() => {
                                            connection.release();
                                            reject(err);
                                        });
                                        return;
                                    }

                                    // 释放连接
                                    connection.release();
                                    resolve(true);
                                });
                            });
                        });
                    });
                });
            });
        });

        return result;
    },
    async updateUserAllowLogin(uid, allowLogin) {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                let sql = `update Users set allowLogin=? where uid=?`;
                connection.query(sql, [allowLogin, uid], function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });

        return result;
    },
    async getUserTypePowerList(userType) {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                let sql = `select
                               p.powerId as powerId,
                               p.name as powerName
                           from UserTypePowerBinding as utpb
                           left join Power as p on p.powerId = utpb.powerId
                           where UserTypeId = (select UserTypeId from UserType where name = ? LIMIT 1)`;
                connection.query(sql, [userType], function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });

        return result;
    },
    async updateUserTypePowerList(userType, powerList) {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return;
                }

                // 开始事务
                connection.beginTransaction(async (err) => {
                    if (err) {
                        connection.release();
                        reject(err);
                        return;
                    }

                    // 操作过程中失败的标志
                    let errorOccurred = false;

                    // 删除这个用户类型对应的所有的用户的权限值
                    let sql1 = `delete from UserPower where uid in (select uid from Users where userTypeId = ?)`;
                    let res1 = new Promise((resolve, reject) => {
                        connection.query(sql1, [userType], (err, result) => {
                            if (err) {
                                errorOccurred = true;
                                // 如果有错误发生，则立即回滚事务
                                connection.rollback(() => {
                                    connection.release();
                                    reject(err);
                                });
                            }
                            resolve(result);
                        });
                    });

                    // 删除用户类型绑定用户权限表里对应的数据
                    let sql2 = `delete from UserTypePowerBinding where userTypeId = ?`;
                    let res2 = new Promise((resolve, reject) => {
                        connection.query(sql2, [userType], (err, result) => {
                            if (err) {
                                errorOccurred = true;
                                // 如果有错误发生，则立即回滚事务
                                connection.rollback(() => {
                                    connection.release();
                                    reject(err);
                                });
                            }
                            resolve(result);
                        });
                    });
                    
                    // 将新权限插入到权限表中
                    let data = '';
                    for (let i = 0; i < powerList.length; i++) {
                        data += `('${userType}', '${powerList[i]}'),`;
                    }
                    data = data.slice(0, -1);
                    let sql3 = `insert into UserTypePowerBinding (
                               userTypeId, powerId
                           ) values ${data}`;
                    let res3 = new Promise((resolve, reject) => {
                        connection.query(sql3, [userType], (err, result) => {
                            if (err) {
                                errorOccurred = true;
                                // 如果有错误发生，则立即回滚事务
                                connection.rollback(() => {
                                    connection.release();
                                    reject(err);
                                });
                            }
                            resolve(result);
                        });
                    });
                    try {
                        await res1;
                        await res2;
                        await res3;
                    } catch (e) {
                        // 有一个插入操作失败，则回滚事务
                        connection.rollback(function () {
                            connection.release();
                            errorOccurred = true;
                            reject(e);
                        });
                    }

                    // 如果所有操作都成功，则提交事务
                    if (!errorOccurred) {
                        connection.commit(err => {
                            if (err) {
                                // 如果提交出错，则回滚事务
                                connection.rollback(() => {
                                    connection.release();
                                    reject(err);
                                });
                                return;
                            }
    
                            // 释放连接
                            connection.release();
                            resolve(true);
                        });
                    } else {
                        reject(false);
                    }
                });
            });
        });

        return result;
    },
    /*
     * 判断用户类型id对应的是哪个用户类型
     */
    async getUserTypeById(userTypeId) {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                let sql = `select name as userTypeName from UserType where userTypeId = ? LIMIT 1`;
                let data = [userTypeId];
                connection.query(sql, data, function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        console.log(err);
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });

        return result;
    },
    /*
     * 获取所有咨询师姓名
     */
    async getAllCounselorsName() {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                let sql = `select
                    u.username as name
                from Counselors as c
                left join Users as u on c.uid = u.uid`;
                let data = [];
                connection.query(sql, data, function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        console.log(err);
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });

        return result;
    },
    async getUidAndWorkIdByUsername(username) {
        let sql = `select uid, workId from Users where username = ?`;
        let data = [username];
        return mysql_tool.execSql(sql, data);
    }
};

module.exports = userDao;
